<?php

class DBManager extends SQLite3 {

    // -------------------------------------------------------------------------

    function __construct(string $pathOfDB) {
        $this->open($pathOfDB);
    }

    // -------------------------------------------------------------------------

    function getFutureDatesWithTask($today) {

        $result = [];
        $arr = [];

        // ---------------------------------------------------------------------
        // 每周
        // ---------------------------------------------------------------------

        $sql = <<<BBB
            select dayOfWeek from weekly
            where TRIM(content) != ""
        BBB;

        $rows = $this->query($sql);
        $arr = [];
        while ($row = $rows->fetchArray()) {
            $arr[] = $row["dayOfWeek"];
        }

        $result["dayOfWeek"] = $arr;

        // ---------------------------------------------------------------------
        // 每月
        // ---------------------------------------------------------------------

        $sql = <<<BBB
            select dayOfMonth from monthly
            where TRIM(content) != ""
        BBB;

        $rows = $this->query($sql);
        $arr = [];
        while ($row = $rows->fetchArray()) {
            $arr[] = $row["dayOfMonth"];
        }

        $result["dayOfMonth"] = $arr;

        // ---------------------------------------------------------------------
        // 每年
        // ---------------------------------------------------------------------

        $sql = <<<BBB
            select monthAndDay from yearly
            where TRIM(content) != ""
        BBB;

        $rows = $this->query($sql);
        $arr = [];
        while ($row = $rows->fetchArray()) {
            $arr[] = $row["monthAndDay"];
        }

        $result["monthAndDay"] = $arr;

        // ---------------------------------------------------------------------
        // 指定日期
        // ---------------------------------------------------------------------

        $sql = <<<BBB
            select date from specificDate
            where (date + 0) > ({$today} + 0) and TRIM(content) != ""
        BBB;

        $rows = $this->query($sql);
        $arr = [];
        while ($row = $rows->fetchArray()) {
            $arr[] = $row["date"];
        }

        $result["specificDate"] = $arr;

        // ---------------------------------------------------------------------

        return $result;

    }

    // -------------------------------------------------------------------------


    // -------------------------------------------------------------------------
    // 数据读取
    // -------------------------------------------------------------------------

    public function readFromGoals() {
        $sql = <<<BBB
            select content from goals
            where id = 1
        BBB;
        return $this->querySingle($sql);
    }

    public function readFromStorageBox() {
        $sql = <<<BBB
            select content from storageBox
            where id = 1
        BBB;
        return $this->querySingle($sql);
    }

    public function readFromDaily() {

        $sql = <<<BBB
            select content from daily
            where id = 1
        BBB;

        return $this->querySingle($sql);

    }

    public function readFromWeekly(int $dayOfWeek) {

        $sql = <<<BBB
            select content from weekly
            where dayOfWeek = $dayOfWeek
        BBB;

        return $this->querySingle($sql);

    }

    public function readFromMonthly(int $dayOfMonth) {

        $sql = <<<BBB
            select content from monthly
            where dayOfMonth = $dayOfMonth
        BBB;

        return $this->querySingle($sql);

    }

    public function readFromYearly(string $monthAndDay) {

        // 注意， monthAndDay 字段的类型是字符串，不是整型，所以要加双引号
        // 不加双引号的话，类似1213这样的，可以正常读取，0203这样的，就会读取不了，因为它被转换成了23
        $sql = <<<BBB
            select content from yearly
            where monthAndDay = "$monthAndDay"
        BBB;

        return $this->querySingle($sql);

    }

    public function readFromSpecificDate(string $date) {

        $sql = <<<BBB
            select content from specificDate
            where date = "$date"
        BBB;

        return $this->querySingle($sql);

    }

    public function readFromFinished(string $date) {

        $sql = <<<BBB
            select content from finished
            where date = "$date"
        BBB;

        return $this->querySingle($sql);

    }

    // -------------------------------------------------------------------------
    // 数据写入
    // -------------------------------------------------------------------------


    public function writeToGoals(string $content) {

        $sql = <<<BBB
            replace into goals (id, content) 
            values (1, "$content")
        BBB;

        $this->exec($sql);

    }

    public function writeToStorageBox(string $content) {

        $sql = <<<BBB
            replace into storageBox (id, content) 
            values (1, "$content")
        BBB;

        $this->exec($sql);

    }

    public function writeToDaily(string $content) {

        $sql = <<<BBB
            replace into daily (id, content) 
            values (1, "$content")
        BBB;

        $this->exec($sql);

    }

    public function writeToWeekly(string $content, int $dayOfWeek) {

        // $dayOfWeek 不需要用引号包括
        // 因为数据库中该字段的类型是整型
        $sql = <<<BBB
            replace into weekly (dayOfWeek, content) 
            values ($dayOfWeek, "$content");
        BBB;

        $this->exec($sql);

    }

    public function writeToMonthly(string $content, int $dayOfMonth) {

        $sql = <<<BBB
            replace into monthly (dayOfMonth, content) 
            values ($dayOfMonth, "$content");
        BBB;

        $this->exec($sql);

    }

    public function writeToYearly(string $content, string $monthAndDay) {

        $sql = <<<BBB
            replace into yearly (monthAndday, content) 
            values ("$monthAndDay", "$content");
        BBB;

        $this->exec($sql);

    }

    public function writeToSpecificDate(string $content, string $date) {

        $sql = <<<BBB
            replace into specificDate (date, content) 
            values ("$date", "$content");
        BBB;

        $this->exec($sql);

    }

    public function writeToFinished(string $content, string $date) {

        $sql = <<<BBB
            replace into finished (date, content)
            values ("$date", "$content");
        BBB;

        $this->exec($sql);

    }


    // -------------------------------------------------------------------------
    // 创建表
    // -------------------------------------------------------------------------

    public function createTables() {

        $sqlCreateDaily = <<<BBB
            CREATE TABLE daily (
                id      INTEGER PRIMARY KEY
                                NOT NULL,
                content TEXT
            );
        BBB;

        $sqlCreateWeekly = <<<BBB
            CREATE TABLE weekly (
                dayOfWeek INTEGER PRIMARY KEY
                                  UNIQUE
                                  NOT NULL,
                content   TEXT
            )
            WITHOUT ROWID;
        BBB;

        $sqlCreateMonthly = <<<BBB
            CREATE TABLE monthly (
                dayOfMonth INTEGER PRIMARY KEY
                                   UNIQUE
                                   NOT NULL,
                content    TEXT
            )
            WITHOUT ROWID;
        BBB;

        $sqlCreateYearly = <<<BBB
            CREATE TABLE yearly (
                monthAndDay TEXT PRIMARY KEY
                                 UNIQUE
                                 NOT NULL,
                content     TEXT
            )
            WITHOUT ROWID;
        BBB;

        $sqlCreateSpecificDate = <<<BBB
            CREATE TABLE specificDate (
                date    TEXT PRIMARY KEY
                             UNIQUE
                             NOT NULL,
                content TEXT
            )
            WITHOUT ROWID;
        BBB;

        $sqlCreateStorageBox = <<<BBB
            CREATE TABLE storageBox (
                id      INTEGER PRIMARY KEY
                                NOT NULL,
                content TEXT
            );
        BBB;

        $sqlCreateGoals = <<<BBB
            CREATE TABLE goals (
                id      INTEGER PRIMARY KEY
                                UNIQUE
                                NOT NULL,
                content TEXT
            );
        BBB;

        $sqlCreateFinished = <<<BBB
            CREATE TABLE finished (
                date    TEXT UNIQUE
                             NOT NULL
                             PRIMARY KEY,
                content TEXT
            );
        BBB;

        $this->exec($sqlCreateStorageBox);
        $this->exec($sqlCreateDaily);
        $this->exec($sqlCreateWeekly);
        $this->exec($sqlCreateMonthly);
        $this->exec($sqlCreateYearly);
        $this->exec($sqlCreateSpecificDate);
        $this->exec($sqlCreateGoals);
        $this->exec($sqlCreateFinished);

    }


    // -------------------------------------------------------------------------
    // 删除表
    // -------------------------------------------------------------------------

    public function deleteTables() {

        $sqlDeleteGoals = <<<BBB
            drop table goals;
        BBB;

        $sqlDeleteStorageBox = <<<BBB
            drop table storageBox;
        BBB;

        $sqlDeleteDaily = <<<BBB
            drop table daily;
        BBB;

        $sqlDeleteWeekly = <<<BBB
            drop table weekly;
        BBB;

        $sqlDeleteMonthly = <<<BBB
            drop table monthly;
        BBB;

        $sqlDeleteYearly = <<<BBB
            drop table yearly;
        BBB;

        $sqlDeleteSpecificDate = <<<BBB
            drop table specificDate;
        BBB;

        $sqlDeleteFinished = <<<BBB
            drop table finished
        BBB;

        $this->exec($sqlDeleteDaily);
        $this->exec($sqlDeleteWeekly);
        $this->exec($sqlDeleteMonthly);
        $this->exec($sqlDeleteYearly);
        $this->exec($sqlDeleteSpecificDate);
        $this->exec($sqlDeleteStorageBox);
        $this->exec($sqlDeleteGoals);
        $this->exec($sqlDeleteFinished);

    }


    // -------------------------------------------------------------------------
    // 重置数据库
    // -------------------------------------------------------------------------

    public function recreateTables() {
        $this->deleteTables();
        $this->createTables();
    }


}
